package zry.Achievement.Standard;

import zry.Search.SearchCriteriaStandard;
import zry.Util.DBUtil;
import Connection.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StandardDAOImpl extends DAOBase implements StandardDAO {
    private static final String STANDARD_INSERT_SQL = "INSERT INTO standard_table(achievement_id,standard_name,standard_type,standard_time,supporting) VALUES(?,?,?,?,?)";
    private static final String STANDARD_UPDATE_SQL = "UPDATE standard_table SET standard_name=?,standard_type=?,standard_time=?,supporting=? WHERE achievement_id=?";
    private static final String STANDARD_SELECT_SQL = "SELECT * FROM standard_table WHERE achievement_id=?";
    private static final String STANDARD_SELECT_ALL_SQL = "SELECT * FROM standard_table";

    @Override
    public boolean addStandard(Standard standard) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(STANDARD_INSERT_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(standard.getA_id()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(standard.getS_name()));
            psmt.setString(3, DBUtil.fixSqlFieldValue(standard.getS_type()));
            psmt.setDate(4, standard.getS_time());
            psmt.setString(5, DBUtil.fixSqlFieldValue(standard.getS_supporting()));
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public boolean updateStandard(Standard standard) {
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(STANDARD_UPDATE_SQL);
            psmt.setString(1, DBUtil.fixSqlFieldValue(standard.getS_name()));
            psmt.setString(2, DBUtil.fixSqlFieldValue(standard.getS_type()));
            psmt.setDate(3, standard.getS_time());
            psmt.setString(4, DBUtil.fixSqlFieldValue(standard.getS_supporting()));
            psmt.setString(5, DBUtil.fixSqlFieldValue(standard.getA_id()));
            psmt.executeUpdate();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    @Override
    public Standard getStandard(String achievement_id) {
        Standard standard = null;
        Connection con = null;
        try {
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(STANDARD_SELECT_SQL);
            psmt.setString(1, achievement_id);
            ResultSet rs = psmt.executeQuery();
            if (rs.next()) {
                standard = new Standard();
                standard.setA_id(rs.getString("achievement_id"));
                standard.setS_name(rs.getString("standard_name"));
                standard.setS_type(rs.getString("standard_type"));
                standard.setS_time(rs.getDate("standard_time"));
                standard.setS_supporting(rs.getString("supporting"));
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return standard;
    }

    public List<Standard> getAllStandards() {
        List<Standard> standards = new ArrayList<>();
        Connection con = null;
        try {
            con = getConnection();
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(STANDARD_SELECT_ALL_SQL);
            while (rs.next()) {
                Standard standard = new Standard();
                standard.setA_id(rs.getString("achievement_id"));
                standard.setS_name(rs.getString("standard_name"));
                standard.setS_type(rs.getString("standard_type"));
                standard.setS_time(rs.getDate("standard_time"));
                standard.setS_supporting(rs.getString("supporting"));
                standards.add(standard);
            }
            rs.close();
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return standards;
    }

    @Override
    public List<Standard> findStandards(SearchCriteriaStandard searchCriteriaStandard) {
        List<Standard> standards = new ArrayList<>();
        Connection con = null;
        try {
            // 构建SQL语句
            StringBuilder sqlBuilder = new StringBuilder("SELECT * FROM standard_table WHERE 1=1");
            if (searchCriteriaStandard.getA_id() != null) {
                sqlBuilder.append(" AND achievement_id LIKE ?");
            }
            if (searchCriteriaStandard.getS_name() != null) {
                sqlBuilder.append(" AND standard_name LIKE ?");
            }
            if (searchCriteriaStandard.getS_type() != null) {
                sqlBuilder.append(" AND standard_type LIKE ?");
            }
            if (searchCriteriaStandard.getS_time() != null) {
                sqlBuilder.append(" AND standard_time LIKE ?");
            }
            if (searchCriteriaStandard.getS_supporting() != null) {
                sqlBuilder.append(" AND supporting LIKE ?");
            }
            String sql = sqlBuilder.toString();

            // 执行查询
            con = getConnection();
            PreparedStatement psmt = con.prepareStatement(sql);
            int index = 1;
            if (searchCriteriaStandard.getA_id() != null) {
                psmt.setString(index++, searchCriteriaStandard.getA_id());
            }
            if (searchCriteriaStandard.getS_name() != null) {
                psmt.setString(index++, searchCriteriaStandard.getS_name());
            }
            if (searchCriteriaStandard.getS_type() != null) {
                psmt.setString(index++, searchCriteriaStandard.getS_type());
            }
            if (searchCriteriaStandard.getS_time() != null) {
                psmt.setDate(index++, searchCriteriaStandard.getS_time());
            }
            if (searchCriteriaStandard.getS_supporting() != null) {
                psmt.setString(index++, searchCriteriaStandard.getS_supporting());
            }
            ResultSet rs = psmt.executeQuery();
            while (rs.next()) {
                String a_id = rs.getString("achievement_id");
                String s_name = rs.getString("standard_name");
                String s_type = rs.getString("standard_type");
                Date s_time = rs.getDate("standard_time");
                String s_supporting = rs.getString("supporting");
                Standard standard = new Standard(a_id, s_name, s_type, s_time, s_supporting);
                standards.add(standard);
            }
            rs.close();
            psmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                con.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        return standards;
    }
}
